package com.jonniespratley.generators.mysql
{
	import mx.messaging.channels.StreamingAMFChannel;

	public class MySQLGen implements IMySQL
	{
		private var _tableColumns:Array = new Array();
		private var _tableValues:Array = new Array();
		private var _template:String;

	 	static public const WHERE_PARAMS:Array = [
	 	{ label: '=', data: '=' },
	 	{ label: '<', data: '<' },
	 	{ label: '<=', data: '<=' },
	 	{ label: '>', data: '>' },
	 	{ label: '>=', data: '>=' },
	 	{ label: '< >', data: '< >' }
	 	/*
	 	,
	 	{ label: 'LIKE', data: 'LIKE' },
	 	{ label: 'NOT LIKE', data: 'NOT LIKE' },
	 	{ label: 'IN', data: 'IN' },
	 	{ label: 'NOT IN', data: 'NOT IN' },
	 	{ label: 'IS NULL', data: 'IS NULL' },
	 	{ label: 'IS NOT NULL', data: 'IS NOT NULL' },
	 	{ label: 'BETWEEN', data: 'BETWEEN' },
	 	{ label: 'NOT BETWEEN', data: 'NOT BETWEEN' }
	 	*/
	 	];
	 	
		static public const GROUPING_PARAMS:Array = [
		{ label: 'AVG', data: 'AVG' },
		{ label: 'COUNT', data: 'COUNT' },
		{ label: 'MAX', data: 'MAX' },
		{ label: 'MIN', data: 'MIN' },
		{ label: 'SUM', data: 'SUM' }];
		
		static public const SORT_PARAMS:Array = [
		{ label: 'ASC', data: 'ASC' },
		{ label: 'DESC', data: 'DESC' } ];

		static private const DB_PATTERN:RegExp = /(@database)/gi;
		static private const TBL_PATTERN:RegExp = /(@table)/gi;
		static private const COL_PATTERN:RegExp = /(@columnnames)/gi;
		static private const VAL_PATTERN:RegExp = /(@columnvalues)/gi;
		static private const EXP_PATTERN:RegExp = /(@expression)/gi;
		static private const COND_PATTERN:RegExp = /(@conditions)/gi;
		static private const WHERE_PATTERN:RegExp = /(@where)/gi;
		static private const ORDERBY_PATTERN:RegExp = /(@order by)/gi;
		static private const LIMIT_PATTERN:RegExp = /(@limit)/gi;
		static private const WHERE_CLEAN_PATTERN:RegExp = /[(int\w*]*\d+\d*\)/gim;



		public function MySQLGen( )
		{

		}

		public function insertStatement( database:String, table:String, columns:Array, values:Array ):String
		{
			//var template:String = getTemplate();
			var template:String = 'INSERT INTO @database.@table (@columnnames) VALUES (@columnvalues)';
			var tempColArray:Array = new Array();
			var tempValArray:Array = new Array();

			for ( var i:int = 0; i < columns.length; i++ )
			{
				tempColArray.push( columns[ i ] );
				tempValArray.push( _prepareString( values[ i ] ) );
			}
			this.setColumns( tempColArray );
			this.setValues( tempValArray );


			var col_name:String = getColumns().toString();
			var val_values:String = getValues().toString();


			//replace all the place holders with the values from the database, table, and column array, and values array;
			var newSQL:String = template.replace( DB_PATTERN, database ).replace( TBL_PATTERN, table ).replace( COL_PATTERN, col_name ).replace( VAL_PATTERN, val_values );

			_clearColumnValues();

			return newSQL;
		}

		/**
		 * 
		 * @param database
		 * @param table
		 * @param columns
		 * @param values
		 * @param condition
		 * @return 
		 * 
		 */		
		public function updateStatement( database:String, table:String, columns:Array, values:Array, condition:Array ):String
		{
			var template:String = 'UPDATE @database.@table SET @columnname = @columnvalue WHERE @condition = @value';
			/*
			UPDATE @database.@table SET @columnname = @columnvalue WHERE @condition = @value
			*/
			for ( var i:int = 0; i < columns.length; i++ )
			{
				setColumns( columns[ i ] );
			}

			return null;
		}

		/**
		 * 
		 * @param database
		 * @param table
		 * @param selectExpr
		 * @param conditionExpr
		 * @return 
		 * 
		 */		
		public function selectStatement( database:String, table:String, selectExpr:Array = null, conditionExpr:Array = null ):String
		{
			//get the template from the getter/setters
			 //var template:String = getTemplate();
			 var template:String = 'SELECT @columnnames FROM @database.@table @WHERE';
			 var cond:String = '';

			//if there is a select expression, (ie. column, column, column, or * ) then lets continue
			if ( selectExpr != null )
			{
				//for each item inside of the selectExpr array lets set the columns
				for ( var i:uint = 0; i < selectExpr.length; i++ )
				{
					setColumns( selectExpr[ i ] );
				}

			}

			//if there is a condtionExpr (ie. WHERE column = value, ORDER BY column ASC | DESC LIMIT value )
			if ( conditionExpr != null )
			{
				//we need to get the key = value from the array
				for ( var j:uint = 0; j < conditionExpr.length; j++ )
				{
					for each ( var pair:Object in conditionExpr )
					{
						for ( var s:String in pair )
						{
							cond += s + pair[ s ]
						}
					}
				}

			} else {
				//if there is no where, remove it from the template
				template = template.replace( WHERE_PATTERN, '' );
			}




			var col_names:String = getColumns().toString();
			var newSQL:String = template.replace( DB_PATTERN, database ).replace( TBL_PATTERN, table ).replace( COL_PATTERN, col_names ).replace( WHERE_PATTERN, 'WHERE ' + cond + '\n').replace( WHERE_CLEAN_PATTERN, '' );


			return newSQL;
		}

		/**
		 * 
		 * @param database
		 * @param table
		 * @param columnValuePair
		 * @return 
		 * 
		 */		
		public function deleteStatement( database:String, table:String, columnValuePair:Array ):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param charset
		 * @param collation
		 * @return 
		 * 
		 */		
		public function alterDatabase( database:String=null, charset:String=null, collation:String=null ):String
		{
			return null;
		}
		
		/**
		 * 
		 * @param database
		 * @param table
		 * @param specification
		 * @return 
		 * 
		 */		
		public function alterTable(database:String, table:String, specification:Array=null):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param charset
		 * @param collation
		 * @return 
		 * 
		 */		
		public function createDatabase(database:String, charset:String=null, collation:String=null):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param table
		 * @param column
		 * @param length
		 * @return 
		 * 
		 */		
		public function createIndex(database:String, table:String, column:String, length:int=11):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param table
		 * @param columns
		 * @return 
		 * 
		 */		
		public function createTable( database:String, table:String, columns:Array ):String
		{

			return null;
		}
		/**
		 * 
		 * @param database
		 * @param ifExists
		 * @return 
		 * 
		 */		
		public function dropDatabase(database:String, ifExists:Boolean=true):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param table
		 * @param index
		 * @return 
		 * 
		 */		
		public function dropIndex(database:String, table:String, index:String):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param table
		 * @param ifExists
		 * @return 
		 * 
		 */		
		public function dropTable(database:String, table:String, ifExists:Boolean=true):String
		{
			return null;
		}
		/**
		 * 
		 * @param database
		 * @param oldTable
		 * @param newTable
		 * @return 
		 * 
		 */		
		public function renameTable(database:String, oldTable:String, newTable:String):String
		{
			return null;
		}
		
		/**
		 * 
		 * @param database
		 * @param table
		 * @param filename
		 * @return 
		 * 
		 */		
		public function loadDataStatement(database:String, table:String, filename:String):String
		{
			return null;
		}

		/**
		 * 
		 * @param database
		 * @param table
		 * @param fromTable
		 * @param columnValuePair
		 * @return 
		 * 
		 */		
		public function selectJoinStatement(database:String, table:String, fromTable:String, columnValuePair:Array=null):String
		{
			return null;
		}
		
		/**
		 * 
		 * @param database
		 * @param table
		 * @param fromTable
		 * @param columnValuePair
		 * @return 
		 * 
		 */		
		public function selectUnionStatement(database:String, table:String, fromTable:String, columnValuePair:Array=null):String
		{
			return null;
		}

		public function getClients():String
		{
			return "SHOW STATUS LIKE '%Aborted%'";
		}

		public function getCommands():String
		{
			return "SHOW STATUS LIKE '%Com%'";
		}

		public function getConnections():String
		{
			return "SHOW STATUS LIKE '%Connections%'";
		}

		public function getInnoDB():String
		{
			return "SHOW STATUS LIKE '%Innodb%'";
		}

		public function getKeys():String
		{
			return "SHOW STATUS LIKE '%Key%'";
		}

		public function getOpen():String
		{
			return "SHOW STATUS LIKE '%Open%'";
		}

		public function getPerformance():String
		{
			return "SHOW STATUS LIKE '%Slow%'";
		}

		public function getQueryCache():String
		{
			return "SHOW STATUS LIKE '%Qcache%'";
		}

		public function getQuestions():String
		{
			return "SHOW STATUS LIKE 'Questions%'";
		}

		public function getReplication():String
		{
			return "SHOW STATUS LIKE '%Slave%'";
		}

		public function getServerInfo():String
		{
			return null;
		}

		public function getShowCommands():String
		{
			return "SHOW STATUS LIKE '%Com_show%'";
		}

		public function getSort():String
		{
			return "SHOW STATUS LIKE '%Sort%'";
		}

		public function getTemp():String
		{
			return "SHOW STATUS LIKE '%tmp%'";
		}

		public function getThreads():String
		{
			return "SHOW STATUS LIKE '%Threads%'";
		}

		public function getSystemStatus():String
		{
			return 'SHOW GLOBAL STATUS';
		}

		public function getSystemVariables():String
		{
			return 'SHOW GLOBAL VARIABLES';
		}

		public function getSystemUsers():String
		{
			return null;
		}

		public function getSystemProcesses():String
		{
			return 'SHOW FULL PROCESSLIST';
		}

		public function getSystemPrivileges():String
		{
			return 'SHOW PRIVILEGES';
		}

		public function showCharacterSet():String
		{
			return null;
		}

		public function showCollation():String
		{
			return null;
		}

		public function showCreateDatabase(database:String):String
		{
			return null;
		}

		public function showCreateProcedure():String
		{
			return null;
		}

		public function showCreateFunction():String
		{
			return null;
		}

		public function showCreateTable(database:String, table:String):String
		{
			return  "SHOW CREATE TABLE";
		}

		public function showCreateView():String
		{
			return null;
		}

		public function showDatabases():String
		{
			return 'SHOW DATABASES';
		}

		public function showEngine():String
		{
			return null;
		}

		public function showEngines():String
		{
			return null;
		}

		public function showErrors():String
		{
			return null;
		}

		public function showGrants():String
		{
			return null;
		}

		public function showIndex(database:String, table:String):String
		{
			return 'SHOW INDEXES FROM clients ' + database+'.'+ table + '';
		}

		public function showOpenTables(database:String):String
		{
			return 'SHOW OPEN TABLES FROM ' + database + '';
		}

		public function showStatus(database:String):String
		{
			return 'SHOW TABLE STATUS FROM ' + database + '';
		}

		public function showTableStatus(database:String):String
		{
			return 'SHOW TABLE STATUS FROM ' + database + '';
		}

		public function showProcesslist():String
		{
			return null;
		}

		public function showWarnings():String
		{
			return null;
		}

		public function showTriggers():String
		{
			return null;
		}

		public function showTables(database:String):String
		{
			return 'SHOW TABLES FROM ' + database + '';;
		}

		private function _clearColumnValues():void
		{
			_tableColumns = new Array();
			_tableValues = new Array();
		}

		public function setTemplate( value:String ):void
		{
			this._template = value;
		}

		public function getTemplate():String
		{
			return _template;
		}

		public function setColumns( value:Object ):void
		{
			this._tableColumns.push( value );
		}

		public function getColumns():Array
		{
			return _tableColumns;
		}

		public function setValues( value:Object ):void
		{
			this._tableValues.push( value );
		}

		public function getValues():Array
		{
			return _tableValues;
		}

		/**
		 * 
		 * @param dirtyQuery
		 * @param database
		 * @return 
		 * 
		 */		
		private function setDatabase( dirtyQuery:String, database:String ):String
		{
			var db_pattern:RegExp = /(@database)/gi;
			var db_value:String = database;

			var newQuery:String = dirtyQuery.replace( db_pattern, db_value );

			return newQuery;
		}

		/**
		 * 
		 * @param dirtyQuery
		 * @param table
		 * @return 
		 * 
		 */		
		private function setTable( dirtyQuery:String, table:String ):String
		{
			return null;
		}

		/**
		 * 
		 * @param str
		 * @return 
		 * 
		 */		
		private function _prepareString( str:String ):String
		{
			var s:String = "'" + str + "'";

			return s;
		}

		/**
		 * 
		 * @param uglyQuery
		 * @return 
		 * 
		 */		
		private function _cleanQuery( uglyQuery:String ):String
		{
			//we need to strip the last comma for now
			var pattern:RegExp = /\,$/m;
			var cleanSql:String = uglyQuery.replace( pattern, '' );

			return cleanSql;
		}

	}
}